﻿using MyUtils.Core;
using PWMIS.DataProvider.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace MyUtils.Module
{
    public partial class sqlinsertcreate : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            cbxIncludeExists.CheckedChanged += cbxIncludeExists_CheckedChanged;
            btnCreate.Click += btnCreate_Click;
            btnTestLink.Click += btnTestLink_Click;
            btnTestData.Click += btnTestData_Click;
        }

        void btnTestData_Click(object sender, EventArgs e)
        {
            AdoHelper db = DBContext.GetDB(tbServer.Text);
            string columns = "";
            string sql = @"
select
     c.name as [字段名],t.name as [字段类型]
     ,convert(bit,c.IsNullable)  as [可否为空]
     ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
         select name from sysindexes where indid in(
             select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
                 as [是否主键]
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]
     ,c.Length as [占用字节] 
     ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度]
     ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
     ,ISNULL(CM.text,'') as [默认值]
     ,isnull(ETP.value,'') AS [字段描述]
     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype 
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' 
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('#tb#')
order by c.id asc";

            sql = sql.Replace("#tb#", ddlTables.SelectedValue.ToString());

            DataTable dtColumns = db.ExecuteDataSet(sql).Tables[0];

            foreach (DataRow dr in dtColumns.Rows)
            {
                string name = dr["字段名"].ToString().ToLower();
                if (string.IsNullOrEmpty(tbExceptColumn.Text))
                    columns += "[" + dr["字段名"] + "],";
                else if (("," + tbExceptColumn.Text + ",").ToLower().IndexOf("," + name + ",") == -1)
                {
                    columns += "[" + dr["字段名"] + "],";
                }
            }
            columns = columns.TrimEnd(',');


            string sqldata = " select top 500 " + columns + " from  [" + ddlTables.SelectedValue.ToString() + "]  where 1=1 and " + (tbWhere.Text.Trim().Length == 0 ? " 1=1 " : tbWhere.Text);
            DataTable dtData = db.ExecuteDataSet(sqldata).Tables[0];
            GridView1.DataSource = dtData;
            GridView1.DataBind();
        }

        void btnTestLink_Click(object sender, EventArgs e)
        {
            AdoHelper db = DBContext.GetDB(tbServer.Text);
            //   string sql = " select q from  " + cbxTables.SelectedText;
            string sqlTbs = "SELECT name FROM SysObjects Where XType='U' order by name  ";

            DataTable dt = db.ExecuteDataSet(sqlTbs).Tables[0];

            ddlTables.DataValueField = "name";
            ddlTables.DataTextField = "name";
            ddlTables.DataSource = dt;
            ddlTables.DataBind();
        }

        void btnCreate_Click(object sender, EventArgs e)
        {
            string tableName = ddlTables.SelectedValue;
            AdoHelper db = DBContext.GetDB(tbServer.Text);
            if (cbxIncludeExists.Checked && string.IsNullOrEmpty(tbIncludeExists.Text))
            {
                throw new Exception("选择了包含Exists，必须填写Exists的判断列名");
            }
            List<string> excludeCoumns = new List<string>();
            excludeCoumns.AddRange(tbExceptColumn.Text.Split(','));

            string sql = @"
select
     c.name as [字段名],t.name as [字段类型]
     ,convert(bit,c.IsNullable)  as [可否为空]
     ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
         select name from sysindexes where indid in(
             select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
                 as [是否主键]
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]
     ,c.Length as [占用字节] 
     ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度]
     ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
     ,ISNULL(CM.text,'') as [默认值]
     ,isnull(ETP.value,'') AS [字段描述]
     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype 
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' 
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('#tb#')
order by c.id asc";

            sql = sql.Replace("#tb#", tableName);
            DataTable dtColumns = db.ExecuteDataSet(sql).Tables[0];
            string columns = "";
            Dictionary<string, string> dic = new Dictionary<string, string>();
            foreach (DataRow dr in dtColumns.Rows)
            {
                string name = dr["字段名"].ToString().ToLower();
                string type = dr["字段类型"].ToString().ToLower();
                dic.Add(name, type);
                if (string.IsNullOrEmpty(tbExceptColumn.Text))
                    columns += "[" + dr["字段名"] + "],";
                else if (("," + tbExceptColumn.Text + ",").ToLower().IndexOf("," + name + ",") == -1)
                {
                    columns += "[" + dr["字段名"] + "],";
                }
            }
            columns = columns.TrimEnd(',');

            string sqldata = " select " + columns + " from  [" + tableName + "] where 1=1 and " + (tbWhere.Text.Trim().Length == 0 ? " 1=1 " : tbWhere.Text);
            //  MessageBox.Show(sqldata);
            string sqlDataAllColumns = " select * from  [" + tableName + "] where 1=1 and " + (tbWhere.Text.Trim().Length == 0 ? " 1=1 " : tbWhere.Text);

            DataTable dtData = db.ExecuteDataSet(sqldata).Tables[0];
            DataTable dtDataAllCOlumns = db.ExecuteDataSet(sqlDataAllColumns).Tables[0];

            // 输出脚本
            StringBuilder sb = new StringBuilder();
            foreach (DataRow dr in dtDataAllCOlumns.Rows)
            {
                string sqlStr = @"insert into [" + tableName + "](";
                if (cbxIncludeExists.Checked)
                {
                    sqlStr = @"if not exists (select * from [#tbname#] where #where#) insert into [#tbname#](";

                    string[] existsColumns = tbIncludeExists.Text.Replace("，", ",").Split(',');
                    string where = "";
                    foreach (DataColumn dc in dtDataAllCOlumns.Columns)
                    {
                        foreach (string column in existsColumns)
                        {
                            if (column.ToLower() == dc.ColumnName.ToLower())
                            {
                                where += " [" + dc.ColumnName + "]=" + GetVal(dr, dc) + " AND";
                            }
                        }
                    }
                    where = where.TrimEnd(new char[] { 'A', 'N', 'D' });
                    sqlStr = sqlStr.Replace("#tbname#", tableName).Replace("#where#", where);
                }
                foreach (DataColumn dc in dtData.Columns)
                {
                    if (("," + tbExceptColumn.Text.ToLower() + ",").IndexOf("," + dc.ColumnName.ToLower() + ",") > -1) continue;
                    sqlStr += "[" + dc.ColumnName + "],";
                }
                sqlStr = sqlStr.TrimEnd(',') + ") values (";
                foreach (DataColumn dc in dtData.Columns)
                {
                    //object v = dr[dc.ColumnName];
                    //if (dc.DataType == typeof(string))
                    //    s += "N'" + v.ToString().Replace("'", "''") + "',";
                    //else if (v.GetType() == typeof(DBNull))
                    //{
                    //    s += "NULL,";
                    //}
                    //else s += v.ToString() + ",";
                    if (("," + tbExceptColumn.Text.ToLower() + ",").IndexOf("," + dc.ColumnName.ToLower() + ",") > -1) continue;
                    sqlStr += GetVal(dr, dc) + ",";
                }
                sqlStr = sqlStr.TrimEnd(',') + ") ";
                sb.AppendLine(sqlStr);
                tbResult.Text = sb.ToString();
            }
        }

        private string GetVal(DataRow dr, DataColumn dc)
        {
            object v = dr[dc.ColumnName];
            string s = "";
            if (dc.DataType == typeof(string))
                s = "N'" + v.ToString().Replace("'", "''") + "'";
            else if (v.GetType() == typeof(DBNull))
            {
                s = "NULL";
            }
            else if (v.GetType() == typeof(DateTime))
            {
                s = "";
            }
            else s = v.ToString() + "";
            return s;
        }

        void cbxIncludeExists_CheckedChanged(object sender, EventArgs e)
        {
            this.spIncludeExists.Visible = cbxIncludeExists.Checked;
        }
    }
}